const fs = require('fs');
const xlsx = require('node-xlsx');
const timestamp = Date.now();
var QcloudSms = require('qcloudsms_js');/// 短信服务
const nodemailer = require('nodemailer'); // 添加这一行
module.exports = function (app, models, $, db, nm) {
  var tencent = new $.Tencent(Config.tencent);
  /// 切换俱乐部列表查询
  app.get('/imgList', async (req, res) => {
    let gameList = await models.pics.listSync(req.query)
    res.send(gameList)
  })
  /// 年检后修改有效时间
  app.post('/v1/changeValidateTime', async (req, res) => {
    console.log('========>req.body', req.body);
    let sql = ''
    if (req.body.type == 0) {
      sql = `
      update member set validateTime='${req.body.validateTime}',unitGrade='${req.body.unitGrade}',img1='${req.body.img1}',img2='${req.body.img2}'
      where id='${req.body.memberId}'
      `
    }
    if (req.body.type == 1) {
      sql = `
      update unit set validateTime='${req.body.validateTime}',unitGrade='${req.body.unitGrade}',img2='${req.body.img1}',img3='${req.body.img2}'
      where memberId='${req.body.memberId}' and status=1
      `
    }
    let ret = await db._evalSync(sql)
    console.log('-------------->ret', ret.changedRows);
    if (ret.changedRows > 0) {
      res.send({ "success": '修改成功' })
    } else {
      res.send({ "errorMsg": '修改失败' })
    }
  })
  /// 导出学员积分记录
  app.post('/v1/exportStuPrecord', async (req, res) => {
    let sql = `
    SELECT p.*,t.name stuName,u.unitId teacherUnitId,u.name unitName FROM psrecord p
    LEFT JOIN member t on t.id = p.userId
    LEFT JOIN unit u on u.unitId = t.unitId
    order by p.createTime desc
    `
    let ret = await db._evalSync(sql)
    if (!ret.errorMsg && ret.length > 0) {
      ret = ret.filter(item => item.role === 2)
      if (req.body.createTimeString) {
        ret = ret.filter(item => item.createTimeString.includes(req.body.createTimeString))
      }
      if (req.body.userId) {
        ret = ret.filter(item => item.userId === req.body.userId)
      } else if (req.body.teacherUnitId) {
        ret = ret.filter(item => item.teacherUnitId === req.body.teacherUnitId)
      }
      ret.forEach(item => {
        if (item.type == 1) {
          item.typeName = '课时转入'
        }
        if (item.type == 2) {
          item.typeName = '装备转入'
        }
        if (item.type == 3) {
          item.typeName = '转出'
        }
        if (item.type == 4) {
          item.typeName = '赛事转入'
        }
        if (item.role == 1) {
          item.roleName = '教练'
        }
        if (item.role == 2) {
          item.roleName = '学员'
        }
      })
      console.log('ret', ret);
      exportData2(ret, res)
    } else {
      res.send({ "errorMsg": '无数据' })
    }
  })
  /// 导出数据1
  function exportData2(data, res) {
    // 数据转换为可导出的格式
    const rows = [
      ['俱乐部', '姓名', '角色', '积分', '类型', '时间'],
      ...data.map(item => [item.unitName, item.stuName, item.roleName, item.score, item.typeName, item.createTimeString])
    ];
    // 创建工作簿
    const workbook = xlsx.build([{ name: 'Sheet1', data: rows }]);
    // 判断目录是否存在
    if (!fs.existsSync('logs')) {
      // 如果不存在就创建目录
      fs.mkdirSync('logs');
    }
    fs.writeFileSync('logs/scoreRecord' + timestamp + '.xlsx', workbook, 'binary');
    let file = '/logs/scoreRecord' + timestamp + '.xlsx'
    res.send({ "path": file });
  }
  /// 导出教练积分记录
  app.post('/v1/exportPrecord', async (req, res) => {
    let sql = `
    SELECT p.*,t.name teacherName,u.unitId teacherUnitId,u.name unitName FROM psrecord p
    LEFT JOIN teacher t on t.id = p.userId
    LEFT JOIN unit u on u.unitId = t.unitId
    order by p.createTime desc
    `
    let ret = await db._evalSync(sql)
    if (!ret.errorMsg && ret.length > 0) {
      ret = ret.filter(item => item.role === 1)
      if (req.body.createTimeString) {
        ret = ret.filter(item => item.createTimeString.includes(req.body.createTimeString))
      }
      if (req.body.userId) {
        ret = ret.filter(item => item.userId === req.body.userId)
      } else if (req.body.teacherUnitId) {
        ret = ret.filter(item => item.teacherUnitId === req.body.teacherUnitId)
      }
      ret.forEach(item => {
        if (item.type == 1) {
          item.typeName = '课时转入'
        }
        if (item.type == 2) {
          item.typeName = '装备转入'
        }
        if (item.type == 3) {
          item.typeName = '转出'
        }
        if (item.type == 4) {
          item.typeName = '赛事转入'
        }
        if (item.role == 1) {
          item.roleName = '教练'
        }
        if (item.role == 2) {
          item.roleName = '学员'
        }
      })
      console.log('ret', ret);
      exportData1(ret, res)
    } else {
      res.send({ "errorMsg": '无数据' })
    }
  })
  /// 导出数据1
  function exportData1(data, res) {
    // 数据转换为可导出的格式
    const rows = [
      ['俱乐部', '姓名', '角色', '积分', '类型', '时间'],
      ...data.map(item => [item.unitName, item.teacherName, item.roleName, item.score, item.typeName, item.createTimeString])
    ];
    // 创建工作簿
    const workbook = xlsx.build([{ name: 'Sheet1', data: rows }]);
    // 判断目录是否存在
    if (!fs.existsSync('logs')) {
      // 如果不存在就创建目录
      fs.mkdirSync('logs');
    }
    fs.writeFileSync('logs/scoreRecord' + timestamp + '.xlsx', workbook, 'binary');
    let file = '/logs/scoreRecord' + timestamp + '.xlsx'
    res.send({ "path": file });
  }
  /// 导出俱乐部下车手的赛事报名记录
  app.post('/v1/exportGameRegPrecord', async (req, res) => {
    let sql = `
SELECT
	b.phone,
	b.NAME,
	unit.NAME AS unitName,
	b.parent,
	b.parentPhone,
	b.createTimeString,
	a.gameNames
FROM
	(
		SELECT
			id,
			IFNULL(NAME, '无') AS NAME,
			IFNULL(phone, '无') AS phone,
			IFNULL(unitid, '无') AS unitid,
			IFNULL(parent, '无') AS parent,
			IFNULL(parentPhone, '无') AS parentPhone,
			IFNULL(createTimeString, '无') AS createTimeString,
			CASE role
		WHEN 0 THEN
			'未实名'
		WHEN 1 THEN
			'审核成功'
		WHEN 2 THEN
			'审核失败'
		WHEN 3 THEN
			'审核中'
		ELSE
			'无'
		END AS role
		FROM
			member
	) b
LEFT JOIN (
	SELECT
		gameReg.userId,
		GROUP_CONCAT(
			gameReg.gameName SEPARATOR ', '
		) AS gameNames
	FROM
		gameReg
	GROUP BY
		gameReg.userId
) a ON a.userId = b.id
LEFT JOIN unit ON b.unitid = unit.unitId
WHERE
	unit.unitId like '${req.body.unitId}%'
ORDER BY
	b.createTimeString DESC
    `
    let ret = await db._evalSync(sql)
    if (!ret.errorMsg && ret.length > 0) {
      console.log('ret:', ret);
      exportData3(ret, res)
    } else {
      res.send({ "errorMsg": '暂无数据' })
    }
  })
  /// 导出数据1
  function exportData3(data, res) {
    // 数据转换为可导出的格式
    const rows = [
      ['手机号', '姓名', '俱乐部', '监护人', '监护人手机', '注册时间', '报名赛事'],
      ...data.map(item => [item.phone, item.NAME, item.unitName, item.parent, item.parentPhone, item.createTimeString, item.gameNames])
    ];
    // 创建工作簿
    const workbook = xlsx.build([{ name: 'Sheet1', data: rows }]);
    // 判断目录是否存在
    if (!fs.existsSync('logs')) {
      // 如果不存在就创建目录
      fs.mkdirSync('logs');
    }
    fs.writeFileSync('logs/scoreRecord' + timestamp + '.xlsx', workbook, 'binary');
    let file = '/logs/scoreRecord' + timestamp + '.xlsx'
    res.send({ "path": file });
  }

  /// 导出核销记录
  app.post('/v1/exportPclass', async (req, res) => {
    let sql = `
    select pclass.*,member.name memberName,t.name teacherName,unit.name memberUnitName,u.name teacherUnitName,u.unitId teacherUnitId from pclass
    LEFT JOIN member on member.id = pclass.userId
    LEFT JOIN teacher t on t.id = pclass.teacher
    LEFT JOIN unit on unit.unitId = member.unitId
    LEFT JOIN unit u on u.unitId = t.unitId
    order by pclass.createTime desc
    `
    let ret = await db._evalSync(sql)
    if (!ret.errorMsg && ret.length > 0) {
      if (req.body.userId) {
        ret = ret.filter(item => item.userId === req.body.userId)
      }
      if (req.body.unitId) {
        ret = ret.filter(item => item.unitId === req.body.unitId)
      }
      if (req.body.teacher) {
        ret = ret.filter(item => item.teacher === req.body.teacher)
      } else if (req.body.teacherUnitId) {
        ret = ret.filter(item => item.teacherUnitId === req.body.teacherUnitId)
      }
      ret.forEach(item => {
        if (item.status == 1) {
          item.statusName = '待核销'
        }
        if (item.status == 2) {
          item.statusName = '已核销'
        }
      })
      console.log('ret', ret);
      exportData(ret, res)
    } else {
      res.send({ "errorMsg": '无数据' })
    }
  })
  /// 导出数据
  function exportData(data, res) {
    // // 要导出的数据
    // const data = [
    //   { name: 'John Doe', email: 'john@example.com', age: 30 },
    //   { name: 'Jane Doe', email: 'jane@example.com', age: 25 }
    // ];
    // // 标题
    // const headers = {
    //   'name': 'Name',
    //   'email': 'Email',
    //   'age': 'Age'
    // };
    // // 数据转换为可导出的格式
    // const rows = [
    //   [headers.name,headers.email,headers.age],
    //   ...data.map(item => [item.name, item.email, item.age])
    // ];
    // 数据转换为可导出的格式
    const rows = [
      ['学员', '学员所属', '课时数', '教练', '教练所属', '时间', '状态'],
      ...data.map(item => [item.memberName, item.memberUnitName, item.number, item.teacherName, item.teacherUnitName, item.createTimeString, item.statusName])
    ];
    // 创建工作簿
    const workbook = xlsx.build([{ name: 'Sheet1', data: rows }]);
    // 判断目录是否存在
    if (!fs.existsSync('logs')) {
      // 如果不存在就创建目录
      fs.mkdirSync('logs');
    }
    fs.writeFileSync('logs/scoreRecord' + timestamp + '.xlsx', workbook, 'binary');
    let file = '/logs/scoreRecord' + timestamp + '.xlsx'
    res.send({ "path": file });
  }

  //新闻查询
  app.get('/newsList', async (req, res) => {
    let sql = `
    select * from article order by createTime desc;
    `
    let ret = await db._evalSync(sql)
    // let newsList = await models.article.listSync(req.query)
    // .slice(0, 4)
    console.log('=============article=ret', ret);
    res.send(ret)
  })
  //年检信息查询list
  app.get('/v1/yearAuthList', async (req, res) => {
    console.log('yearAuthList=====', req.query);
    let limitt = 'order by u.createTime desc';
    if (req.query.pageNum) {
      let pageNum = (parseInt(req.query.pageNum) - 1) * req.query.pageSize
      limitt = 'order by u.createTime desc limit ' + pageNum + ',' + req.query.pageSize
    }
    let sql1 = `
    select u.*,m.name memberName,m.phone memberPhone,a.name unitName from pyearauth u
    left join member m on m.id = u.userId
    left join unit a on a.unitId = u.unitId
    ${limitt}
    `
    let sql2 = `
    select u.*,m.name memberName,m.phone memberPhone from pyearauth u
    left join member m on m.id = u.userId
    left join unit a on a.unitId = u.unitId
    `
    let List = await db._evalSync(sql1)
    let List1 = await db._evalSync(sql2)
    console.log('============ret==List1', List1);
    res.send({ rows: List, total: List1.length })
  })
  //车手信息查询list
  app.get('/v1/cheshouList', async (req, res) => {
    console.log('cheshouList=====', req);
    let limitt = 'order by u.createTime desc';
    if (req.query.pageNum) {
      let pageNum = (parseInt(req.query.pageNum) - 1) * req.query.pageSize
      limitt = 'order by u.createTime desc limit ' + pageNum + ',' + req.query.pageSize
    }
    let sql1 = `
    select u.*,m.name memberName,m.phone memberPhone from cheshou u
    left join member m on m.id = u.memberId
    ${limitt}
    `
    let sql2 = `
    select u.*,m.name memberName,m.phone memberPhone from cheshou u
    left join member m on m.id = u.memberId
    `
    let List = await db._evalSync(sql1)
    let List1 = await db._evalSync(sql2)
    console.log('============ret==List1', List1);
    res.send({ rows: List, total: List1.length })
  })
  //俱乐部信息查询list
  app.get('/v1/teacherList', async (req, res) => {
    console.log('teacherList=====', req);
    let limitt = 'order by u.createTime desc';
    if (req.query.pageNum) {
      let pageNum = (parseInt(req.query.pageNum) - 1) * req.query.pageSize
      limitt = 'order by u.createTime desc limit ' + pageNum + ',' + req.query.pageSize
    }
    let sql1 = `
    select u.*,m.name memberName,m.phone memberPhone from teacher u
    left join member m on m.id = u.memberId
    ${limitt}
    `
    let sql2 = `
    select u.*,m.name memberName,m.phone memberPhone from teacher u
    left join member m on m.id = u.memberId
    `
    let List = await db._evalSync(sql1)
    let List1 = await db._evalSync(sql2)
    console.log('============ret==List1', List1);
    res.send({ rows: List, total: List1.length })
  })
  //俱乐部信息查询list
  app.get('/v1/jlbList', async (req, res) => {
    console.log('jlbList=====', req);
    let limitt = 'order by u.createTime desc';
    if (req.query.pageNum) {
      let pageNum = (parseInt(req.query.pageNum) - 1) * req.query.pageSize
      limitt = 'order by u.createTime desc limit ' + pageNum + ',' + req.query.pageSize
    }
    let sql1 = `
    select u.*,m.name memberName,m.phone memberPhone from unit u
    left join member m on m.id = u.memberId
    ${limitt}
    `
    let sql2 = `
    select u.*,m.name memberName,m.phone memberPhone from unit u
    left join member m on m.id = u.memberId
    `
    let List = await db._evalSync(sql1)
    let List1 = await db._evalSync(sql2)
    console.log('============ret==List1', List1);
    res.send({ rows: List, total: List1.length })
  })
  //车手信息查询list
  app.get('/v1/getCheShouInfo', async (req, res) => {
    console.log('getCheShouInfo=====', req);
    let sql1 = `
    select * from cheshou
    where memberId = '${req.query.memberId}' and grade>0 and status = ${req.query.status}
    order by createTime desc
    limit 1
    `
    let List1 = await db._evalSync(sql1)
    console.log('============ret==getCheShouInfo', List1);
    res.send(List1)
  })
  //用户信息查询list
  app.get('/v1/getUserInfo', async (req, res) => {
    console.log('getUserInfo=====', req.query);
    let sql1 = `
    select m.*,u.name unitName from member m
    LEFT JOIN unit u on u.unitId=m.unitId and u.status=1
    where m.id = '${req.query.memberId}' 
    order by m.createTime desc
    limit 1
    `
    let List1 = await db._evalSync(sql1)
    console.log('============ret==getUserInfo', List1);
    res.send(List1)
  })
  //已报名赛事查询
  app.get('/v1/mygameList', async (req, res) => {
    console.log('mygamelist', req.query);
    let sql = `
    select * from unit where memberId='${req.query.memberId}' and status = 1;
    `
    let unitList = await db._evalSync(sql)
    let sql1
    if (unitList.length > 0) {
      console.log('是俱乐部负责人');
      sql1 = `
      select * from gameReg where unitId in ('${unitList.map(val => val.unitId).join(',')}') order by createTime desc;
      `
    } else {
      console.log('不是负责人');
      sql1 = `
      select * from gameReg where userId='${req.query.memberId}' order by createTime desc;
      `
    }
    let gameList1 = await db._evalSync(sql1)
    console.log('-----gameList1---', gameList1);
    if (gameList1.length > 0) {
      let ret = gameList1.map(val => val.gameId)
      // if (ret.length > 0) {
      let sql2 = `
        select * from game where id in (${ret}) order by createTime desc;
        `
      let ret1 = await db._evalSync(sql2)
      console.log('============ret==gameList', ret1);
      res.send(ret1)
    } else {
      res.send([])
    }
  })
  //赛事查询
  app.get('/gameList', async (req, res) => {
    let sql = `
    select * from game order by startTime desc;
    `
    let ret = await db._evalSync(sql)
    // let gameList = await models.game.listSync(req.query)
    console.log('============ret==gameList', ret);
    res.send(ret)
  })
  //赛事查询
  app.get('/v1/gameList', async (req, res) => {
    // console.log('req.body',req);
    let gameList = await models.pics.listSync(req.query)
    res.send(gameList)
  })
  /// 课时对换积分
  app.post('/v1/pclassend', async (req, res) => {
    ///找课时算积分，然后更新核销状态，更新用户积分
    let setting = await models.setting.listSync({ key1: 'class2score' })///查询1课时算多少积分
    if (!setting.errorMsg) {
      let value1 = setting[0].value1///查询1课时算积分
      let cclass = req.body
      let score = parseInt(cclass.number) * value1
      let teacher1 = await models.teacher.listSync({ id: cclass.teacher })///查询用户积分
      if (!teacher1.errorMsg) {
        let res2 = await models.teacher.updateSync({ id: cclass.teacher }, { score: parseInt(score) + parseInt(teacher1[0].score) });///更新教练积分
        if (!res2.errorMsg) {
          let res1 = await models.pclass.updateSync({ id: cclass.id }, { status: 2 });///更新核销状态
          if (!res1.errorMsg) {
            let paramm = {}
            paramm.userId = cclass.teacher
            paramm.score = score
            paramm.type = 1
            let res3 = await models.psrecord.insertSync(paramm);/// 新增积分记录
            if (!res3.errorMsg) {
              res.send({ success: res3 })
            } else {
              res.send({ errorMsg: '更新用户积分失败' })
            }
          } else {
            res.send({ errorMsg: '更新核销状态失败' })
          }
        } else {
          res.send({ errorMsg: '修改教练积分失败' })
        }
      } else {
        res.send({ errorMsg: '未查到教练积分' })
      }
    } else {
      res.send({ errorMsg: '请设置class2score的值' })
    }
  })
  /// 装备购买对换积分
  app.post('/v1/pzb2score', async (req, res) => {
    ///找课时算积分，然后更新核销状态，更新用户积分
    let setting = await models.setting.listSync({ key1: 'zb2score' })///查询1课时算多少积分
    if (!setting.errorMsg) {
      let value1 = setting[0].value1///查询装备价格算积分
      let cclass = req.body
      let score = parseInt(cclass.totalFee) * value1
      let teacher = await models.teacher.listSync({ id: cclass.teacher })///查询教练积分
      if (!teacher.errorMsg) {
        let res2 = await models.teacher.updateSync({ id: cclass.teacher }, { score: parseInt(score) + parseInt(teacher[0].score) });///更新教练积分
        if (!res2.errorMsg) {
          let paramm = {}
          paramm.userId = cclass.teacher
          paramm.score = score
          paramm.type = 2
          let res3 = await models.psrecord.insertSync(paramm);/// 新增积分记录
          if (!res3.errorMsg) {
            res.send({ success: res3 })
          } else {
            res.send({ errorMsg: '更新用户积分失败' })
          }
        } else {
          res.send({ errorMsg: '修改教练积分失败' })
        }
      } else {
        res.send({ errorMsg: '未查到教练积分' })
      }
    } else {
      res.send({ errorMsg: '请设置zb2score的值' })
    }
  })
  /// 检查邮箱是否已注册接口
  app.post('/checkEmailExists', async (req, res) => {
    try {
      const { email } = req.body;
      const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;

      // 验证邮箱格式
      if (!email || !emailRegex.test(email)) {
        return res.send({ errorMsg: '请输入正确的邮箱地址' });
      }

      // 检查邮箱是否已被注册
      let emailUser = await models.member.findSync({ 'email': email }).catch(err => {
        console.error('查询邮箱失败：', err);
        return { errorMsg: '系统错误，请稍后重试' };
      });

      if (!emailUser.errorMsg) {
        // 邮箱已被注册
        return res.send({ errorMsg: '该邮箱已被注册' });
      } else {
        // 邮箱未被注册
        return res.send({ success: '邮箱可用' });
      }
    } catch (error) {
      console.error('检查邮箱异常：', error);
      res.send({ errorMsg: '系统错误，请稍后重试' });
    }
  });
  /// 随机生成6位验证码
  function getVerifyCode() {
    var code = '';
    for (var i = 0; i < 6; i++) {
      code += Math.floor(Math.random() * 10).toString();
    }
    return code;
  }
  /// 发送邮件验证码
  app.post('/sendEmailCode', async (req, res) => {
    try {
      // 验证邮箱格式
      const email = req.body.email;
      const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;

      if (!email || !emailRegex.test(email)) {
        return res.send({ errorMsg: '请输入正确的邮箱地址' });
      }

      // 生成验证码
      let code = getVerifyCode();
      console.log(email, '邮件验证码：', code);

      // 创建邮件发送器
      const transporter = nodemailer.createTransport({
        host: Config.email.host,
        port: Config.email.port,
        secure: Config.email.secure,
        auth: {
          user: Config.email.auth.user,
          pass: Config.email.auth.pass
        }
      });

      // 邮件内容
      const mailOptions = {
        from: Config.email.from,
        to: email,
        subject: '验证码',
        text: `您的验证码是：${code}，有效期为5分钟，请勿泄露给他人。`,
        html: `<p>您的验证码是：<strong>${code}</strong></p><p>有效期为5分钟，请勿泄露给他人。</p>`
      };

      // 发送邮件
      transporter.sendMail(mailOptions, async (error, info) => {
        if (error) {
          console.error('发送邮件失败：', error);
          res.send({ errorMsg: '发送验证码失败，请稍后重试' });
        } else {
          console.log('邮件已发送：', info.response);

          // 数据库添加邮件验证码记录（如果有相应的表）
          // 如果没有 email_code 表，您可能需要先创建它
          let emailCodeRecord = await models.emailcode.insertSync({
            'email': email,
            'verifycode': code
          }).catch(err => {
            console.warn('保存邮件验证码记录失败：', err);
            // 继续执行，不影响主流程
          });

          res.send({ success: '验证码已发送' });
        }
      });

    } catch (error) {
      console.error('发送邮件验证码异常：', error);
      res.send({ errorMsg: '系统错误，请稍后重试' });
    }
  });


  /// 注册接口
  app.post('/register', async (req, res) => {
    try {
      const { email, password, code, openId } = req.body;
      const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;

      // 1. 验证参数
      if (!email || !emailRegex.test(email)) {
        return res.send({ errorMsg: '请输入正确的邮箱地址' });
      }

      if (!password) {
        return res.send({ errorMsg: '请输入密码' });
      }

      if (!code) {
        return res.send({ errorMsg: '请输入验证码' });
      }

      if (!openId) {
        return res.send({ errorMsg: '获取用户信息失败，请重试' });
      }

      // 2. 验证验证码是否有效（5分钟内）
      const currentTime = new Date().getTime();
      let emailCode = await models.emailcode.findSync({
        'email': email,
        'verifycode': code,
        'createTime': { $gt: currentTime - 5 * 60 * 1000 }
      }).catch(err => {
        console.error('查询验证码失败：', err);
        return { errorMsg: '验证码验证失败' };
      });

      if (emailCode.errorMsg) {
        return res.send({ errorMsg: '验证码错误或已过期' });
      }

      // 3. 根据openId查找用户
      let existingUser = await models.member.findSync({ 'openId': openId }).catch(err => {
        console.error('查询用户失败：', err);
        return { errorMsg: '系统错误，请稍后重试' };
      });

      if (existingUser.errorMsg) {
        return res.send({ errorMsg: '用户不存在，请先登录微信' });
      }

      // 4. 检查邮箱是否已被其他用户使用
      let emailUser = await models.member.findSync({
        'email': email,
        'id': { $ne: existingUser.id } // 排除当前用户
      }).catch(err => {
        console.error('查询邮箱失败：', err);
        return { errorMsg: '系统错误，请稍后重试' };
      });

      if (!emailUser.errorMsg) {
        return res.send({ errorMsg: '该邮箱已被其他账号使用' });
      }

      // 5. 更新用户信息（不新增用户）
      let updatedUser = await models.member.updateSync(
        { 'id': existingUser.id },
        {
          'email': email,
          'passwd': password
        }
      ).catch(err => {
        console.error('更新用户失败：', err);
        return { errorMsg: '注册失败，请稍后重试' };
      });

      if (updatedUser.errorMsg) {
        return res.send({ errorMsg: updatedUser.errorMsg });
      }

      // 6. 查询更新后的用户信息
      let userInfo = await models.member.findSync({ 'id': existingUser.id }).catch(err => {
        console.error('查询用户信息失败：', err);
        return { errorMsg: '系统错误' };
      });

      if (userInfo.errorMsg) {
        return res.send({ success: '注册成功，请登录' });
      }

      // 7. 直接登录并返回用户信息
      // memberLogin(req, res, userInfo);

    } catch (error) {
      console.error('注册异常：', error);
      res.send({ errorMsg: '系统错误，请稍后重试' });
    }
  });
  /// 邮箱密码登录接口
  app.post('/wxnewlogin', async (req, res) => {
    try {
      const { email, password } = req.body;
      const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/;

      // 1. 验证参数
      if (!email || !emailRegex.test(email)) {
        return res.send({ errorMsg: '请输入正确的邮箱地址' });
      }

      if (!password) {
        return res.send({ errorMsg: '请输入密码' });
      }

      // 2. 根据邮箱查询用户
      let user = await models.member.findSync({ 'email': email }).catch(err => {
        console.error('查询用户失败：', err);
        return { errorMsg: '系统错误，请稍后重试' };
      });

      if (user.errorMsg) {
        return res.send({ errorMsg: '用户不存在' });
      }

      // 3. 验证密码
      if (user.passwd !== password) {
        return res.send({ errorMsg: '用户名或密码错误' });
      }

      // 4. 调用memberLogin函数完成登录会话设置
      memberLogin(req, res, user);

    } catch (error) {
      console.error('登录异常：', error);
      res.send({ errorMsg: '系统错误，请稍后重试' });
    }
  });

  /////////////////////////////////////////////////////////////////////////////////////

  /// 获取、发送验证码
  app.post('/getVerifyCode', async (req, res) => {
    let code = getVerifyCode();
    console.log(req.body.phone, 'b验证码：', code);
    /// 短信
    var qcloudsms = QcloudSms(Config.tencent.smsAppId, Config.tencent.smsAppKey);
    ///验证码为：{1}，您正在登录，若非本人操作，请勿泄露。
    /// 国家码、短信模板id、参数list、腾讯云的签名
    qcloudsms.SmsMultiSender().sendWithParam('86', [req.body.phone], 2154816, [code], '中汽赛智体育', '', '', async function (err, res1, resData) {
      if (!resData.errorMsg) {
        /// 数据库添加短信
        let sms = await models.sms.insertSync({
          'phone': req.body.phone,
          'verifycode': code
        })
        if (!sms.errorMsg) {
          // 短信发送成功，添加数据库成功
          res.send({ success: '验证码已发送' })
        } else {
          res.send({ errorMsg: sms.errorMsg })
        }
      } else {
        res.send({ errorMsg: resData.errorMsg })
      }
    })
  })
  /// 获取当前时间
  function getTimeFormart() {
    const currentTime = new Date();
    const year = currentTime.getFullYear();
    const month = String(currentTime.getMonth() + 1).padStart(2, '0');
    const day = String(currentTime.getDate()).padStart(2, '0');
    const hours = String(currentTime.getHours()).padStart(2, '0');
    const minutes = String(currentTime.getMinutes()).padStart(2, '0');
    const seconds = String(currentTime.getSeconds()).padStart(2, '0');
    const milliseconds = currentTime.getMilliseconds().toString().padStart(3, '0'); // 毫秒
    const formattedTime = year + '-' + month + '-' + day + ' ' + hours + ':' + minutes + ':' + seconds;
    return formattedTime
  }

  /// 手机号登陆
  app.post('/wxlogin', async (req, res) => {
    let member = await models.member.findSync({ 'phone': req.body.phone })  // 手机号查询用户
    let member1 = await models.member.findSync({ 'openId': req.body.openId })  // openId查询用户
    console.log('----member-->', member);
    console.log('----member1-->', member1);
    const currentTime = new Date().getTime(); // 当前时间
    // 查找sms中5分钟以内的记录
    let sms = await models.sms.findSync({ 'phone': req.body.phone, 'verifycode': req.body.verifycode, 'createTime': { $gt: currentTime - 5 * 60 * 1000 } })
    console.log('----sms-->', sms);
    if (!sms.errorMsg) { // 验证码正确
      console.log('验证码正确');
      if (member.errorMsg) { // 报错，手机号未绑定用户，新增用户
        console.log('手机号不存在，新增用户');
        if (!member1.errorMsg) { // openId绑定了用户
          let resa1 = await models.member.updateSync(
            {
              'id': member1.id
            }, {
            'openId': ''
          })
          console.log('-------resa1-->', resa1);
          if (resa1.errorMsg) {
            res.send({ errorMsg: '解除绑定失败' })
          }
        }
        let resa = await models.member.insertSync({
          'phone': req.body.phone,
          'openId': req.body.openId
        })
        if (!resa.errorMsg) {
          console.log('新增用户成功');
          let member = await models.member.findSync({ 'phone': req.body.phone })  // 手机号查询用户
          member.errorMsg = false
          memberLogin(req, res, member)
        } else {
          console.log('新增用户失败', resa.errorMsg);
          res.send({ errorMsg: '新增用户失败' })
        }
      } else { // 手机号存在，解除之前的绑定
        console.log('手机号存在,修改用户openId');
        if (!member1.errorMsg) { // openId绑定了用户
          let resa1 = await models.member.updateSync(
            {
              'id': member1.id
            }, {
            'openId': ''
          })
          console.log('-------resa1-->', resa1);
          if (resa1.errorMsg) {
            res.send({ errorMsg: '解除绑定失败' })
          }
        }
        // openId绑定登陆用户
        let resa = await models.member.updateSync(
          {
            'id': member.id
          }, {
          'openId': req.body.openId
        })
        if (!resa.errorMsg) {
          memberLogin(req, res, member)
        } else {
          res.send({ errorMsg: '修改用户openId失败' })
        }
        // }
      }
    } else { // 验证码错误
      res.send({ errorMsg: '验证码错误' })
    }
  })
  /// 绑定微信
  // app.post('/bindWx', async (req, res) => {
  //   let member = await models.member.findSync({ 'phone': req.body.phone })  // 手机号查询用户 req.body.phone
  //   let member1 = await models.member.findSync({ 'openId': req.body.openId })  // openId查询用户 req.body.openId
  //   if (!member.errorMsg) { // 查出用户
  //     if (!member1.errorMsg) {
  //       res.send({ success: '微信已绑定用户', user: true })
  //     } else {
  //       let memberUp = await models.member.updateSync(
  //         { 'id': member.id },
  //         { 'openId': req.body.openId })  // 修改用户openId 
  //       console.log('修改用户openId', memberUp);
  //       res.send(memberUp)
  //     }
  //   } else { // 查询用户失败
  //     console.log('查询用户失败', member);
  //     res.send(member)
  //   }
  // })
  /// 赛事报名
  app.post('/v1/batchBaoMing', async (req, res) => {
    res.send([])
  })
  /// 赛事报名
  app.post('/baomingGame', async (req, res) => {
    // 查找sms中是否存在记录
    let gameReg = await models.gameReg.findSync({ 'userId': req.body.userId, 'gameId': req.body.gameId })
    console.log('gameReg1:', gameReg);
    if (!gameReg.errorMsg) {
      res.send({ errorMsg: '已报名' })
    } else {
      // 新增一个报名记录，向gameReg中插入数据
      let gameReg = await models.gameReg.insertSync(req.body)
      console.log('gameReg', gameReg);
      if (!gameReg.errorMsg) {
        res.send({ success: '报名成功' })
      } else {
        res.send({ errorMsg: '报名失败' })
      }

    }
  })
  /// 用户机构信息
  app.post('/memberUnitInfo', async (req, res) => {
    // 查找sms中是否存在记录
    let member = await models.member.findSync({ 'userId': req.body.userId })
    console.log('member:', member);
    if (!member.errorMsg) {
      let unit = await models.unit.findSync({ 'unitId': member.unitId })
      if (unit.errorMsg) {
        res.send({ errorMsg: unit.errorMsg })
      }
      res.send({ success: unit })
    } else {
      res.send({ errorMsg: member.errorMsg })
    }
  })

  //会员登录
  function memberLogin(req, res, member) {
    console.log('member=======>', member);
    var memberInfo = member;
    let userId = null
    memberInfo.id = member.id
    userId = member.id
    nm.setSessionRouter(req, res, {
      id: member.session_key,
      unitId: member.unitId,
      userId: userId,
      userName: member.openId,
      phone: member.phone,
      platform: 'WeChatMiniApp',
      open_kfid: member.open_kfid,
      unitName: member.unitName,
      unitLogo: member.unitLogo,
      type: 2
    }, ret => {
      if (!ret.errorMsg) { // setSession成功
        console.log('------->setSession成功', ret);
        memberInfo.token = ret.id;
        res.send(memberInfo);
      } else if (/Duplicate entry/.test(ret.errorMsg)) { // 已经存在session
        console.log('------->已经存在session');
        models.session.find({ id: member.session_key }, function (ret) {
          if (ret.id) memberInfo.token = ret.id;
          res.send(ret.errorMsg ? ret : memberInfo);
        })
      } else { // 抛错
        res.send(ret);
      }
    })
  }

  /// 俱乐部人数
  app.get('/v1/a1List', async (req, res) => {
    let sql = `
    select * from 
    ( SELECT
      unit.NAME,
      mm.num
     FROM
      ( 
        SELECT
          unitId,
          count(unitId) num
        FROM
          member
        GROUP BY
          unitId
      ) mm
     LEFT JOIN unit ON unit.unitId = mm.unitId
     ORDER BY mm.num desc
    ) t
    order by t.num
    limit 10;
    `
    let ret = await db._evalSync(sql)
    console.log(ret);
    res.send(ret);
  })
  /// 俱乐部教练积分排名
  // app.get('/v1/a2List', async (req, res) => {
  //   let sql = `
  //   SELECT
  //   u.NAME,
  //   b.ssum
  //   FROM
  //     (
  //       SELECT
  //         t.unitId,
  //         sum(t.score) ssum
  //       FROM
  //         teacher t
  //       GROUP BY
  //         t.unitId
  //       HAVING
  //         ssum > 0
  //     ) b
  //   LEFT JOIN unit u ON u.unitId = b.unitId
  //   order by ssum
  //   limit 10;
  //   `
  //   let ret = await db._evalSync(sql)
  //   console.log(ret);
  //   res.send(ret);
  // })
  /// 俱乐部学员积分排名
  app.get('/v1/a3List', async (req, res) => {
    let sql = `
    SELECT
    u.NAME,
    b.ssum
    FROM
      (
        SELECT
          t.unitId,
          sum(t.score) ssum
        FROM
          member t
        GROUP BY
          t.unitId
        HAVING
          ssum > 0
      ) b
    LEFT JOIN unit u ON u.unitId = b.unitId
    order by ssum
    limit 10;
    `
    let ret = await db._evalSync(sql)
    console.log(ret);
    res.send(ret);
  })

  // 获取设置
  app.post('/getSettings', async (req, res) => {
    let sql = `
    SELECT * from setting;
    `
    let ret = await db._evalSync(sql)
    console.log(ret);
    res.send(ret);
  })
  // 订单支付
  app.post('/v1/payOrder', async (req, res) => {
    orderActions.payOrder(req.body.orderId, data => {
      console.log('payData', data);
      res.send(data);
    })
    // let ret = await db._evalSync(sql)
    // console.log(ret);
    // res.send(ret);
  })
  //微信支付结果通知
  app.get('/v1/wxpay/notify', tencent.WXPay.useWXCallback(function (ret, req, res, next) {
    console.log('my.js-notify');
    if (ret.result_code == 'SUCCESS') {
      orderActions.updateOrderPay(ret.out_trade_no, ret.transaction_id, ret.attach, function (ret) {
        if (!ret.errorMsg) {
          res.success();
        } else {
          res.fail();
        }
      })
    } else {
      res.fail();
    }
  }))
  //微信支付结果查询
  app.get('/v1/wxpay/result', function (req, res) {
    models.order.find({ id: req.query.orderId }, function (ret) {
      if (ret.id) {
        var totalFee = ret.totalFee;
        if (ret.status == 20 && ret.transaction_id) {
          res.send(ret);
        } else {
          var query = {};
          if (ret.transaction_id) {
            query.transaction_id = ret.transaction_id;
          } else {
            query.out_trade_no = ret.id;
          }
          tencent.WXPay.queryOrder(query, function (err, ret) {
            if (ret.return_code == 'SUCCESS') {
              orderActions.updateOrderPay(ret.out_trade_no, ret.transaction_id, function (ret) {
                ret.totalFee = totalFee;
                res.send(ret);
              })
            }
          })
        }
      } else {
        res.send({ errorMsg: ret.errorMsg || ret.prepay_id ? '查询支付结果出错!' : '该订单未支付' });
      }
    })
  })

  // 订单通用
  var orderActions = {
    //取消订单
    cancelOrder: function (orderId, next) {
      models.order.update({ id: orderId }, { status: 50 }, function (ret) {
        console.log('释放库存完成,更新订单', ret)
        next(ret);
      })
      //关闭交易单
      tencent.WXPay.closeOrder({ out_trade_no: orderId }, function () { })
    },
    //自动取消订单
    autoCancelOrder: function (next) {
      console.log('开始自动结束未支付订单，释放库存', new Date());
      // models.order.list({status:{$in:[0,10]},createTime:{$lt:new Date().getTime() - autoTimes.cancel * 60 * 1000}},function(ret){
      //   console.log('需要取消的订单',ret)
      //   if(!ret.errorMsg && ret.length) for(var i = 0; i < ret.length; i ++) orderActions.cancelOrder(ret[i].id,function(){})
      // })
    },
    //支付订单
    payOrder: function (orderId, next, args) {
      args = args || {};
      //预下单
      models.order.find({ id: orderId }, function (ret) {
        if (ret.id) { // 预下单成功
          if (ret.status > 10) {
            next({ errorMsg: '订单号:' + ret.id + ',订单状态:' + ret.status + ',无法进行支付' })
            return false;
          }
          var options = {
            openid: ret.openId,
            body: '购买商品',
            detail: '无',
            attach: '',
            out_trade_no: ret.id,
            trade_type: 'JSAPI',
            total_fee: ret.totalFee * 100,
            spbill_create_ip: '',
            notify_url: Config.hosting + '/wxpay/notify'
          }
          tencent.WXPay.createUnifiedOrder(options, function (err, ret) {
            if (ret && ret.result_code == 'SUCCESS') { // 支付成功
              var miniAppPay = tencent.WXPay.createMiniAppPay({ out_trade_no: options.out_trade_no, prepay_id: ret.prepay_id });
              models.order.update({ id: orderId }, {
                status: 10,
                prepay_id: ret.prepay_id
              }, function (ret) {
                if (ret.errorMsg) {
                  ret.nuu = 1
                  next(ret)
                } else {
                  miniAppPay.nuu = 2
                  next(miniAppPay)
                }
              })
            } else if (ret.err_code == 'ORDERPAID') {//订单已支付
              tencent.WXPay.queryOrder({ out_trade_no: options.out_trade_no }, function (err, ret) {
                if (ret.return_code == 'SUCCESS') {
                  orderActions.updateOrderPay(ret.out_trade_no, ret.transaction_id, function (ret) {
                    next({ errorMsg: '订单已支付', code: 'ORDERPAID' });
                  })
                }
              })
            } else {// 支付失败
              next({ errorMsg: ret.err_code_des || ret.return_msg || '生成支付订单失败', n1: ret, n2: ret.return_msg })
            }
          })
        } else {// 预下单失败
          ret.nuu = 4
          next(ret)
        }
        // }, {
        //   join: {
        //     productId: {
        //       type: 'LEFT',
        //       name: 'product',
        //       key: '_id',
        //       projection: {
        //         name: 'productName'
        //       }
        //     }
        //   }
      })
    },
    //更新支付状态
    updateOrderPay: function (orderId, transaction_id, next, other) {
      other = other || {};
      models.order.update({ id: orderId }, { ...other, transaction_id, status: 100 }, function (ret) {
        if (ret.errorMsg) {
          if (typeof next == 'function') next(ret);
        } else {
          //更新功能单状态
          orderActions.updateFunctionOrderPay(orderId, typeof next == 'function' ? next : function () { });
        }
      })
    },
    //更新功能单状态
    updateFunctionOrderPay: function (orderId, next) {
      models.order.find({ id: orderId, status: 100 }, function (ret) {
        if (ret.errorMsg) {
          next(ret);
        } else if (ret.functionality == 'addShareTimes') {//律师分享包
          var times = 100;
          db._eval(`SET SQL_SAFE_UPDATES = 0;update lowyer set shareTimes=shareTimes + ${times} where memberId=${ret.memberId};SET SQL_SAFE_UPDATES = 1;`, function (ret) {
            console.log('修改分享次数', ret)
            next(ret)
          });
        } else if (ret.functionality == 'resources') {//学院资源添加记录
          models.resources.find({ productId: ret.productId }, resources => {
            models.myResources.insert({
              memberId: ret.memberId,
              resourcesId: resources._id,
              resourcesTypeId: resources.resourcesTypeId,
              orderId: ret.id
            }, ret1 => {
              if (ret1.errorMsg) console.log('添加购买资源失败', ret1, ret)
              next(ret1)
            })
          })
        } else if (ret.functionality == 'testList') {///父订单更新子订单
          models.order.update({ transaction_id: 'PORDERPAYED_' + ret.id }, { status: 100 }, next)
        } else {
          next({})
        }
      })
    }
  }

}